package com.ejoysoft.wlanyz.business;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.ejoysoft.common.DbConnect;
import com.ejoysoft.common.Format;
import com.ejoysoft.common.Globa;
import com.ejoysoft.common.UID;
//import com.pub.File;
import jxl.*;
import java.io.File;
/**
 * Created by IntelliJ IDEA.
 * User: feiwj
 * Date: 2005-9-1
 * Time: 9:12:43
 * To change this template use Options | File Templates.
 */
public class Flowanalysis {
    private Globa globa;
    private DbConnect db;

    //���췽��
    public Flowanalysis() {
    }

    public Flowanalysis(Globa globa) {
        this.globa = globa;
        db = globa.db;
    }

    //���췽��
    public Flowanalysis(Globa globa, boolean b) {
        this.globa = globa;
        db = globa.db;
        if (b) globa.setDynamicProperty(this);
    }

    String strTableName = "WLAN_RESC_NETSFLOWS";
    String strTableName2 = "sys_syspara";

    //增加用户
    public boolean add() {
        String strSql = "";
        strId = UID.getID();
        try {
        	//把用户信息写入数据库
            strSql = "INSERT INTO " + strTableName + "  (strId,strCity,strTown,strNetType,strCorreType,strDataFlow,startTime,strCreator,dtCreatDate) VALUES (?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,to_date(?,'yyyy-MM-dd hh24:mi:ss'))";
            db.prepareStatement(strSql);
            db.setString(1, strId);
            db.setString(2, strCity);
            db.setString(3, strTown);
            db.setString(4, strNetType);
            db.setString(5, strCorreType);
            db.setDouble(6, strDataFlow);
            db.setString(7, startTime);
            System.err.println(startTime+"PPPPPPPPPPPPPPPPP");
            db.setString(8, globa.loginName);
            db.setString(9, Format.getDateTime());
            if (db.executeUpdate() > 0) {
                Globa.logger0("增加业务流量信息", globa.loginName, globa.loginIp, strSql, "用户管理", globa.userSession.getStrDepart());
                return true;
            } else
                return false;
        } catch (Exception e) {
            System.out.println("增加业务流量信息时出错！！");
            e.printStackTrace();
            return false;
        }
    }

  //详细显示单条记录
    public Flowanalysis show(String where) {
        try {
            String strSql = "select * from  " + strTableName + "  ".concat(where);
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null && rs.next())
                return load(rs, true);
            else
                return null;
        } catch (Exception ee) {
            return null;
        }
    }
    //获取各个网络类型的业务流量之和
    public double flowSum(String where,String netType){
    	double flowSum = 0;
    	try {
			String sql = "select sum(strdataflow) from "+strTableName+" ".concat(where)+" and strnettype = '"+netType +"' ";
			ResultSet rs = db.executeQuery(sql);
			if (rs != null && rs.next()) {
				flowSum = rs.getDouble(1);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return flowSum;
    }

    
    public boolean delete(String where) {
        try {
            String sql = "DELETE FROM " + strTableName + "  ".concat(where);
            db.executeUpdate(sql);
            Globa.logger0("删除业务流量信息", globa.loginName, globa.loginIp, sql, "业务流量管理", globa.unitCode);
            return true;
        } catch (Exception ee) {
            ee.printStackTrace();
            return false;
        }
    }

    public boolean update(String where) {
        try {
            String strSql = "UPDATE  " + strTableName + "  SET  strCity = ?,strTown = ?, strNetType = ?,strCorreType = ?,strDataFlow = ?,startTime = to_date(?,'yyyy-MM-dd hh24:mi:ss'), strCreator = ?, dtCreatDate = to_date(?,'yyyy-MM-dd hh24:mi:ss')  ";
            strSql =  strSql.concat(where);
            db.prepareStatement(strSql);
            db.setString(1, strCity);
            db.setString(2, strTown);
            db.setString(3, strNetType);
            db.setString(4, strCorreType);
            db.setDouble(5, strDataFlow);
            db.setString(6, startTime);
            db.setString(7, globa.loginName);
            db.setString(8, Format.getDateTime());
            db.executeUpdate();
            Globa.logger0("修改业务流量信息", globa.loginName, globa.loginIp, strSql, "业务流量管理", globa.userSession.getStrDepart());
            return true;
        } catch (Exception e) {
            System.out.println("修改业务流量信息时出错：" + e);
            e.printStackTrace();
            return false;
        }
    }

  //记录集转化为对象
    public Flowanalysis load(ResultSet rs, boolean isView) {
        Flowanalysis theBean = new Flowanalysis();
        try {
        	theBean.setStrId(rs.getString("strId"));
        	theBean.setStrCity(rs.getString("strcity"));
        	theBean.setStrTown(rs.getString("strtown"));
        	theBean.setStrNetType(rs.getString("strNetType"));
        	theBean.setStrCorreType(rs.getString("strCorreType"));
        	theBean.setStrDataFlow(rs.getDouble("strDataFlow"));
        	theBean.setStartTime(rs.getString("startTime"));
        	theBean.setStrCreator(rs.getString("strCreator"));
            theBean.setDtCreatDate(rs.getString("dtCreatDate"));

        } catch (Exception e) {
            e.printStackTrace();
        }
        return theBean;
    }
    //记录集转化为对象
    public Flowanalysis loadMap(ResultSet rs, boolean isView) {
        Flowanalysis theBean = new Flowanalysis();
        try {
        	theBean.setStrCity(rs.getString("strcity"));
        	theBean.setStrTown(rs.getString("strtown"));
        	theBean.setStrCorreType(rs.getString("strCorreType"));

        } catch (Exception e) {
            e.printStackTrace();
        }
        return theBean;
    }

  //查询符合条件的记录总数
    public int getCount(String where) {
        int count = 0;
        try {
            String sql = "SELECT count(strId) FROM " + strTableName + "  ";
            if (where.length() > 0) {
                where = where.toLowerCase();
                if (where.indexOf("order") > 0)
                    where = where.substring(0, where.lastIndexOf("order"));
                sql = String.valueOf(sql) + String.valueOf(where);
            }
            ResultSet rs = db.executeQuery(sql);
            if (rs.next())
                count = rs.getInt(1);
            rs.close();
            return count;
        } catch (Exception ee) {
            ee.printStackTrace();
            return count;
        }
    }


  //列表记录集
    public Vector list(String where, int startRow, int rowCount) {
        Vector beans = new Vector();
        try {
            String sql = "SELECT *  FROM  " + strTableName + " ";
            if (where.length() > 0)
                sql = String.valueOf(sql) + String.valueOf(where);
            Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (startRow != 0 && rowCount != 0)
                s.setMaxRows((startRow + rowCount) - 1);
          //System.out.println("333333333333333"+sql);
            ResultSet rs = s.executeQuery(sql);
            if (rs != null && rs.next()) {
                if (startRow != 0 && rowCount != 0)
                    rs.absolute(startRow);
                do {
                    Flowanalysis theBean = new Flowanalysis();
                    theBean = load(rs, false);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
            s.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }
        return beans;
    }
    
  //列表记录集在地图显示不重复的地点
    public Vector listMap(String where,String strStartTime,String strEndTime) {
        Vector beans = new Vector();
        //String andType = " and strnettype = '"+type+"' ";
        try {
        	   if(strStartTime.equals(""))strStartTime = "1000-1-01";
        	    if(strEndTime.equals(""))strEndTime = "9999-12-30";
        	    where+=" and starttime between to_date('" + strStartTime + "','yyyy-MM-dd hh24.mi.ss') and to_date('" + strEndTime + "','yyyy-MM-dd hh24.mi.ss') ";
            String sql = "SELECT distinct strcity,strtown,strcorretype  FROM  " + strTableName + " ";
                sql = String.valueOf(sql) + String.valueOf(where);
                System.err.println("ZZZZZZZZZZZZZ"+sql);
            ResultSet rs = db.executeQuery(sql);
            if (rs != null && rs.next()) {
                do {
                    Flowanalysis theBean = new Flowanalysis();
                    theBean = loadMap(rs, false);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }
        return beans;
    }
    
  //通过地址拿出地址的网络类型名称
    public ArrayList<String> getTypeByAddr(String city,String town,String corretype,String strStartTime,String strEndTime )
    {
    	if(strStartTime.equals(""))strStartTime = "1000-1-01";
	    if(strEndTime.equals(""))strEndTime = "9999-12-30";
	   String where = " and starttime between to_date('" + strStartTime + "','yyyy-MM-dd hh24.mi.ss') and to_date('" + strEndTime + "','yyyy-MM-dd hh24.mi.ss') ";
    	DbConnect dbConnect = new DbConnect();
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(strnettype) from "+ strTableName+" where strcity = '"+city+"' and strtown = '"+town+"' and strcorretype ='"+corretype+"' ";
    	sql = String.valueOf(sql)+String.valueOf(where);
    	System.err.println(sql+"=========");
    	ResultSet re = dbConnect.executeQuery(sql);
    	System.err.println("++++++++++++");
    	try {
    	
			if(re != null && re.next())
			{   //String country = re.getString("");
				do{
					String type = re.getString("strnettype");
					System.err.println(type+"++++++++++++");
					beans.add(type);
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			dbConnect.closeCon();
		}
    	return beans;
    }
    //拿出一个地址特定网络类型一个时间段内的流量
    public double getFlowsNum(String city,String town,String corretype,String strStartTime,String strEndTime,String type){
    	double flowsNum = 0;
    	if(strStartTime.equals(""))strStartTime = "1000-1-01";
	    if(strEndTime.equals(""))strEndTime = "9999-12-30";
	   String where = " and starttime between to_date('" + strStartTime + "','yyyy-MM-dd hh24.mi.ss') and to_date('" + strEndTime + "','yyyy-MM-dd hh24.mi.ss') ";
    	DbConnect dbConnect = new DbConnect();
    	try {
			String sql = "select sum(strdataflow) from "+ strTableName +" where strcity = '"+city+"' and strtown = '"+town+"' and strcorretype ='"+corretype+"' and" +
					" strnettype = '"+type+"' "; 
	    	sql = String.valueOf(sql)+String.valueOf(where);
	    	System.err.println(sql+"=========");
	    	ResultSet rs = dbConnect.executeQuery(sql);
	    	if(rs != null && rs.next())
			{   //String country = re.getString("");
				do{
					 flowsNum = rs.getDouble(1);
				}while(rs.next());
			}
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			dbConnect.closeCon();
		}
		return flowsNum;
    }
    
  //调出所有地市名称
    public ArrayList<String> getAllCitiesByName( )
    {
    	//System.err.println(where+"=========");
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(strCity) from "+ strTableName+" ";
    	ResultSet re = db.executeQuery(sql);
    	try {
    	
			if(re != null && re.next())
			{   //String country = re.getString("");
				do{
					String city = re.getString("strCity");
					//System.err.println(city+"++++++++++++");
					beans.add(city);
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
  //调出所有县市名称
    public ArrayList<String> getAllTownsByName(String city )
    {
    	//System.err.println(where+"=========");
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(strTown) from "+ strTableName+" where strcity = '"+city+"'";
    	//System.err.println(sql+";;;;;;");
    	ResultSet re = db.executeQuery(sql);
    	try {
    	
			if(re != null && re.next())
			{   //String country = re.getString("");
				do{
					String town = re.getString("strTown");
					beans.add(town);
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
  //调出所有网络类型名称
    public ArrayList<String> getAllTypeByName( )
    {
    	//System.err.println(where+"=========");
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(strvalue) from "+ strTableName2+" where strname = '网络类型'";
    	ResultSet re = db.executeQuery(sql);
    	//System.err.println(sql+"LLLLLLLLLLLLLLLLLL");
    	try {
    	
			if(re != null && re.next())
			{   //String country = re.getString("");
				do{
					String nettype = re.getString("strvalue");
					String type[] = nettype.split(",");
					for (int i = 0; i < type.length; i++) {
						//System.err.println(type[i]+"?????????");
						beans.add(type[i]);
					}
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
   // String sql = "select * from task_info t where t.dtcreattime=(select max(dtcreattime) from task_info where strmac=t.strmac )";
    //
    public String getTime(String city,String town,String corretype,String strStartTime,String strEndTime,String type){
    	String startTime = "";
    	DbConnect dbConnect = new DbConnect();
    	try {
    		if(strStartTime.equals(""))strStartTime = "1000-1-01";
    	    if(strEndTime.equals(""))strEndTime = "9999-12-30";
    	   String where = " and starttime between to_date('" + strStartTime + "','yyyy-MM-dd hh24.mi.ss') and to_date('" + strEndTime + "','yyyy-MM-dd hh24.mi.ss') ";
			String sql = "select min(starttime) from "+strTableName+ " where strcity = '"+city+"' and strtown = '"+town+"' and strcorretype ='" +
					""+corretype+"' and strnettype = '"+type+"' ";
			sql = String.valueOf(sql)+String.valueOf(where);
			ResultSet rs = dbConnect.executeQuery(sql);
			if (rs!=null&&rs.next()) {
				do {
					startTime = rs.getString(1);
				} while (rs.next());
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			dbConnect.closeCon();
		}
		return startTime;
    }
    
    public void method(String url){
    	try
	    {
	    List<String> list = new ArrayList<String>();
	    Workbook workBook=Workbook.getWorkbook(new File(url));
	    //获得第一个工作表对象
	    Sheet sheet=workBook.getSheet(0);
	    
	    //得到第一列第一行的单元格
	    Cell cell=null;
	
	    
	    for (int i = 1; i < sheet.getRows(); i++){
	        List<String> list2 = new ArrayList<String>();
	        for (int j = 0; j < sheet.getColumns(); j++) {
	        	cell = sheet.getCell(j, i);
	        	list2.add(cell.getContents().toString());//将每行的字符串用一个String类型的集合保存。
	        }
	        System.err.println(list2.get(0)+list2.get(1)+list2.get(2)+list2.get(3)+Double.parseDouble(list2.get(4))+list2.get(5));
	        insertinto(list2.get(0),list2.get(1),list2.get(2),list2.get(3),Double.parseDouble(list2.get(4)),list2.get(5));
	    }
	    	workBook.close();//记得关闭
	      
	      //迭代集合查看每行的数据
		    
		    
	    }catch(Exception e)
	    {
	    System.out.println(e);
	    e.printStackTrace();
	    }
    }
    
    //********************导入**************************************
    public void insertinto(String strCity,String strTown,String strNetType,String strCorreType,Double strDataFlow,String startDate){
    	String strSql = "";
    	strId = UID.getID();
    	//DbConnect dbConnect = new DbConnect();
		try {
    		String strTempId = UID.getID();
    		strSql = "INSERT INTO " + strTableName + "  (strId,strCity,strTown,strNetType,strCorreType,strDataFlow,startTime,strCreator,dtCreatDate) " +
    				"VALUES (?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,to_date(?,'yyyy-MM-dd hh24:mi:ss'))";
	        db.prepareStatement(strSql);
	        db.setString(1, strTempId);
            db.setString(2, strCity);
            db.setString(3, strTown);
            db.setString(4, strNetType);
            db.setString(5, strCorreType);
            db.setDouble(6, strDataFlow);
            db.setString(7, startDate);
            db.setString(8, globa.loginName);
            db.setString(9, Format.getDateTime());
	        if (db.executeUpdate() > 0) {
	        	Globa.logger0("导入excel表格成功", globa.loginName, globa.loginIp, strSql, "任务管理", globa.userSession.getStrDepart());
	        	
	        } 
		} catch (Exception e) {
			//dd.rollback();
	        e.printStackTrace();
	        //dd.closeCon();
		}
		//db.closeCon();
    }


    private String strId;//	
    private String strCity;//	
    private String strTown;//	
    private String strNetType;//	
    private String strCorreType;//网元名称
    private double strDataFlow;//	数据流量
    private String startTime;//开始时间
    private String strCreator;//	
    private String dtCreatDate;//	操作时间
    
 

    public Vector setAll(Vector<Flowanalysis> objWlan, Vector<Flowanalysis> objTd, Vector<Flowanalysis> objGsm){
    	Vector  beans = new Vector();
    	for(int i = 0; i<objWlan.size(); i++){
    		beans.add(objWlan.get(i));
    	}
    	for(int i = 0; i<objTd.size(); i++){
    		beans.add(objTd.get(i));
    	}
    	for(int i = 0; i<objGsm.size(); i++){
    		beans.add(objGsm.get(i));
    	}
    	return beans;
    }
 

	public String getStrId() {
		return strId;
	}

	public void setStrId(String strId) {
		this.strId = strId;
	}

	public String getStrCity() {
		return strCity;
	}

	public void setStrCity(String strCity) {
		this.strCity = strCity;
	}

	public String getStrTown() {
		return strTown;
	}

	public void setStrTown(String strTown) {
		this.strTown = strTown;
	}

	public String getStrNetType() {
		return strNetType;
	}

	public void setStrNetType(String strNetType) {
		this.strNetType = strNetType;
	}

	public String getStrCorreType() {
		return strCorreType;
	}

	public void setStrCorreType(String strCorreType) {
		this.strCorreType = strCorreType;
	}

	public String getStartTime() {
		return startTime;
	}

	public void setStartTime(String startTime) {
		this.startTime = startTime;
	}

	public String getStrCreator() {
		return strCreator;
	}

	public void setStrCreator(String strCreator) {
		this.strCreator = strCreator;
	}

	public String getDtCreatDate() {
		return dtCreatDate;
	}

	public void setDtCreatDate(String dtCreatDate) {
		this.dtCreatDate = dtCreatDate;
	}

	public double getStrDataFlow() {
		return strDataFlow;
	}

	public void setStrDataFlow(double strDataFlow) {
		this.strDataFlow = strDataFlow;
	}


    
}
